import logging
from be.model import error
from be.model import db_conn
import json
from sqlalchemy.exc import SQLAlchemyError
from pymongo.errors import PyMongoError
from sqlalchemy.sql import text
from be.model.times import get_time_stamp

class Seller(db_conn.DBConn):
    def __init__(self):
        db_conn.DBConn.__init__(self)
    
    # 上架书籍
    def add_book(self, user_id: str, store_id: str, book_id: str, book_json_str: str, stock_level: int):
     mongo_session = self.mongo.client.start_session()
     try:
        with self.conn.begin():
            if not self.user_id_exist(user_id):
                return error.error_non_exist_user_id(user_id)
            if not self.store_id_exist(store_id):
                return error.error_non_exist_store_id(store_id)
            if self.store_book_id_exist(store_id, book_id):
                return error.error_exist_book_id(book_id)

            cursor = self.conn.execute(
                text("SELECT * FROM book WHERE id = :book_id;"),
                {"book_id": book_id}
            )
            row = cursor.fetchone()

            if row:
                price = row[8]
                self.conn.execute(
                    text("""
                        INSERT INTO store_book (store_id, book_id, stock_level, price)
                        VALUES (:store_id, :book_id, :stock_level, :price)
                    """),
                    {
                        "store_id": store_id,
                        "book_id": book_id,
                        "stock_level": stock_level,
                        "price": price
                    }
                )
            else:
                book_info = json.loads(book_json_str)
                price = book_info.get("price")

                self.conn.execute(
                    text("""
                        INSERT INTO book (
                            id, title, author, publisher, original_title, translator, pub_year,
                            pages, price, currency_unit, binding, isbn, tags
                        ) VALUES (
                            :id, :title, :author, :publisher, :original_title, :translator, :pub_year,
                            :pages, :price, :currency_unit, :binding, :isbn, :tags
                        )
                    """),
                    {
                        "id": book_info["id"],
                        "title": book_info["title"],
                        "author": book_info["author"],
                        "publisher": book_info["publisher"],
                        "original_title": book_info.get("original_title", ""),
                        "translator": book_info.get("translator", ""),
                        "pub_year": book_info.get("pub_year", ""),
                        "pages": book_info.get("pages", 0),
                        "price": price,
                        "currency_unit": book_info.get("currency_unit", ""),
                        "binding": book_info.get("binding", ""),
                        "isbn": book_info.get("isbn", ""),
                        "tags": book_info.get("tags", "")
                    }
                )

                self.conn.execute(
                    text("""
                        INSERT INTO store_book (store_id, book_id, stock_level, price)
                        VALUES (:store_id, :book_id, :stock_level, :price)
                    """),
                    {
                        "store_id": store_id,
                        "book_id": book_id,
                        "stock_level": stock_level,
                        "price": price
                    }
                )

                with mongo_session.start_transaction():
                 book_details = {
                        "book_id": book_id,
                        "author_intro": book_info.get("author_intro", ""),
                        "book_intro": book_info.get("book_intro", ""),
                        "content": book_info.get("content", ""),
                        "pictures": book_info.get("pictures", None)  
                    }
                 self.mongo['book_details'].update_one(
                          {"book_id": book_id},  
                          {"$set": book_details}, 
                          upsert=True,       
                          session=mongo_session
                                                        )
        return 200, "ok"

     except SQLAlchemyError as e:
        return 528, f"PostgreSQL 错误: {str(e)}"

     except PyMongoError as e:
         logging.error(f"MongoDB 错误: {str(e)}")

     except Exception as e:
        return 530, f"未知错误: {str(e)}"

     finally:
        mongo_session.end_session()

    # 增加库存
    def add_stock_level(self, user_id: str, store_id: str, book_id: str, add_stock_level: int):
        try:
            with self.conn.begin():
                if not self.user_id_exist(user_id):
                    return error.error_non_exist_user_id(user_id)
                if not self.store_id_exist(store_id):
                    return error.error_non_exist_store_id(store_id)
                if not self.store_book_id_exist(store_id, book_id):
                    return error.error_non_exist_book_id(book_id)

                self.conn.execute(text("UPDATE store_book SET stock_level = stock_level + :asl  WHERE store_id = :sid AND book_id = :bid"),
                                {'asl': add_stock_level, 'sid': store_id, 'bid': book_id})
            return 200, "ok"

        except SQLAlchemyError as e:
            return 528, "{}".format(str(e))
        except BaseException as e:
            return 530, "{}".format(str(e))
    
    # 开店
    def create_store(self, user_id: str, store_id: str) -> tuple[int, str]:
        try:
            with self.conn.begin():
                if not self.user_id_exist(user_id):
                    return error.error_non_exist_user_id(user_id)
                if self.store_id_exist(store_id):
                    return error.error_exist_store_id(store_id)
                
                self.conn.execute(text("INSERT into user_store (store_id, user_id) VALUES (:sid, :uid)"), {'sid': store_id, 'uid': user_id})

            return 200, "ok"

        except SQLAlchemyError as e:
            return 528, "{}".format(str(e))
        except BaseException as e:
            return 530, "{}".format(str(e))
    
    # 确认发货
    def send_books(self, store_id, order_id):
        try:
            with self.conn.begin():
                if not self.store_id_exist(store_id):
                    return error.error_non_exist_store_id(store_id)
                if not self.order_id_exist(order_id):
                    return error.error_invalid_order_id(order_id)
                
                cursor = self.conn.execute(text(
                    "SELECT status FROM orders where order_id = :order_id"), {"order_id": order_id})
                row = cursor.fetchone()
                status = row[0]
                if status != 2:
                    return error.error_invalid_order_status(order_id)

                self.conn.execute(text(
                    "UPDATE orders set status=3, shipped_at = :current_time where order_id = :order_id ;"), 
                    {"order_id": order_id, "current_time": get_time_stamp()})

            return 200, "ok"

        except SQLAlchemyError as e:
            return 528, "{}".format(str(e))
        except BaseException as e:
            return 530, "{}".format(str(e))
        